/***************************************************************************
 * 文件名:ConceptsDao.java<br>
 * 版本: <br>
 * 描述: <br>
 * 版权所有: <br>
 * //////////////////////////////////////////////////////// <br>
 * 创建者: robo<br>
 * 创建日期: 2010-11-15 <br>
 * 修改者: <br>
 * 修改日期: <br>
 * 修改说明: <br>
 **************************************************************************/
package com.sunwayhorizo.health.ets.base.service.dao;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.ejb.Stateless;
import javax.persistence.Query;

import org.jboss.seam.annotations.Name;

import com.sunwayhorizo.health.business.dto.DictionaryDTO;
import com.sunwayhorizo.health.common.ListUtil;
import com.sunwayhorizo.health.common.StrUtil;
import com.sunwayhorizo.health.ets.base.common.BaseDao;
import com.sunwayhorizo.health.ets.base.persistent.EtConcepts;

@Stateless
@Name("ConceptsDao")
public class ConceptsDao extends BaseDao implements IConceptsDao {

	public List<EtConcepts> findConcepts(String[] as) {
		String jpql = "select o from EtConcepts  o where 1=1 ";
		if (as != null && as.length > 0) {
			jpql += " and ( ";
			for (int i = 0; i < as.length; i++) {

				if (i == as.length - 1) {
					jpql += " o.conceptCode='" + as[i] + "' ";
				} else {
					jpql += " o.conceptCode='" + as[i] + "'  or ";
				}
			}
			jpql += " ) ";
		}
		Query query = entityManager2.createQuery(jpql);
		List<EtConcepts> list = query.getResultList();
		return list;
	}

	public List<DictionaryDTO> getConceptsObjects(String codingschemeEnName) {

		if (codingschemeEnName == null || codingschemeEnName.equals("")) {
			return null;
		}

		String jpql = "select  ecs.codingschemeChName,ecs.codingschemeEnName,ecs.codingschemeOid,ev.versionName,o.conceptName ,o.conceptCode ,o.inputPy,o.inputWb  "
				+ "from EtCodingSchemes ecs,EtVersions ev, EtConcepts o "
				+ "where  ecs.codingschemeId = ev.etCodingSchemes.codingschemeId and ev.versionId = o.etVersions.versionId and ecs.codingschemeEnName=:codingschemeEnName ";
		Query query = entityManager2.createQuery(jpql);
		query.setParameter("codingschemeEnName", codingschemeEnName);
		List<Object> list = query.getResultList();
		List<DictionaryDTO> etsList = getDictionaryDtoByObject(list);
		return etsList;
	}

	public List<DictionaryDTO> getConceptsObjects(String codingschemeChName,
			String codingschemeEnName, String versionName) {

		String jpql = "select  ecs.codingschemeChName,ecs.codingschemeEnName,ecs.codingschemeOid,ev.versionName,o.conceptName ,o.conceptCode ,o.inputPy,o.inputWb "
				+ "from EtCodingSchemes ecs,EtVersions ev, EtConcepts o "
				+ "where  ecs.codingschemeId = ev.etCodingSchemes.codingschemeId and ev.versionId = o.etVersions.versionId  ";

		if (codingschemeChName != null && !codingschemeChName.equals("")) {
			jpql += " and ecs.codingschemeChName=:codingschemeChName ";
		}

		if (codingschemeEnName != null && !codingschemeEnName.equals("")) {
			jpql += " and ecs.codingschemeEnName=:codingschemeEnName ";
		}
		if (versionName != null && !versionName.equals("")) {
			jpql += " and ev.versionName=:versionName ";
		}
		Query query = entityManager2.createQuery(jpql);
		if (codingschemeChName != null && !codingschemeChName.equals("")) {
			query.setParameter("codingschemeChName", codingschemeChName);
		}
		if (versionName != null && !versionName.equals("")) {
			query.setParameter("versionName", versionName);
		}

		List<Object> list = query.getResultList();
		List<DictionaryDTO> etsList = getDictionaryDtoByObject(list);
		return etsList;
	}

	public List<DictionaryDTO> getConceptsObjects(String codingschemeEnName,
			String versionName) {
		String jpql = "select  ecs.codingschemeChName,ecs.codingschemeEnName,ecs.codingschemeOid,ev.versionName,o.conceptName ,o.conceptCode,o.inputPy,o.inputWb ,ev.retiredDate,o.retiredDate"
				+ " from EtCodingSchemes ecs,EtVersions ev, EtConcepts o "
				+ "where  ecs.codingschemeId = ev.etCodingSchemes.codingschemeId and ev.versionId = o.etVersions.versionId  and ev.statusCode='1' and o.statusCode = '1' ";

		if (codingschemeEnName != null && !codingschemeEnName.equals("")) {
			jpql += " and ecs.codingschemeEnName=:codingschemeEnName ";
		}
		if (versionName != null && !versionName.equals("")) {
			jpql += " and ev.versionName=:versionName ";
		}
		Query query = entityManager2.createQuery(jpql);

		if (codingschemeEnName != null && !codingschemeEnName.equals("")) {
			query.setParameter("codingschemeEnName", codingschemeEnName);
		}
		if (versionName != null && !versionName.equals("")) {
			query.setParameter("versionName", versionName);
		}
		/*query.setParameter("sysdate1", new Date(), TemporalType.DATE);
		query.setParameter("sysdate2", new Date(), TemporalType.DATE);*/
		List<Object> list = query.getResultList();

		List<DictionaryDTO> etsList = getDictionaryDtoByObject(list);
		return etsList;
	}
	
    @SuppressWarnings("unchecked")
    public String getSimpleConceptsObjects(String codingschemeEnName, String versionName)
    {
        String jpql = "select o.inputPy,o.conceptName,o.conceptCode,o.conceptId "
                + " from EtCodingSchemes ecs join ecs.etVersionsList as ev join ev.etConceptsList as o "
                + "where ev.statusCode='1' and o.statusCode = '1' ";

        if (StrUtil.isNotEmpty(codingschemeEnName))
        {
            jpql += " and ecs.codingschemeEnName=:codingschemeEnName ";
        }
        if (StrUtil.isNotEmpty(versionName))
        {
            jpql += " and ev.versionName=:versionName ";
        }else{
            jpql += " and ev.defaultFlag='1' ";
        }
        jpql += " order by o.inputPy ";
        Query query = entityManager2.createQuery(jpql);

        if (StrUtil.isNotEmpty(codingschemeEnName))
        {
            query.setParameter("codingschemeEnName", codingschemeEnName);
        }
        if (StrUtil.isNotEmpty(versionName))
        {
            query.setParameter("versionName", versionName);
        }
        List<Object> list = query.getResultList();
        if( ListUtil.isEmpty(list)){
            return null;
        }
        /*final Collator cmp = Collator.getInstance(java.util.Locale.CHINA);
        Collections.sort(list, new Comparator(){
            @Override
            public int compare(Object o1, Object o2)
            {
                Object[] oo1 =(Object[])o1;
                Object[] oo2 =(Object[])o2;
                return cmp.compare(oo1[4],oo2[4]);
            }
        });*/
        return getSimpleDictionaryAsXmlByString(list);
    }
	
	public DictionaryDTO getConceptsObject(String codingschemeEnName,
			String conceptCode) {
		String jpql = "select  ecs.codingschemeChName,ecs.codingschemeEnName,ecs.codingschemeOid,ev.versionName,o.conceptName ,o.conceptCode,o.inputPy,o.inputWb  "
				+ "from EtCodingSchemes ecs,EtVersions ev, EtConcepts o "
				+ "where  ecs.codingschemeId = ev.etCodingSchemes.codingschemeId and ev.versionId = o.etVersions.versionId  ";

		if (codingschemeEnName != null && !codingschemeEnName.equals("")) {
			jpql += " and ecs.codingschemeEnName=:codingschemeEnName ";
		}
		if (conceptCode != null && !conceptCode.equals("")) {
			jpql += " and o.conceptCode=:conceptCode ";
		}
		Query query = entityManager2.createQuery(jpql);

		if (codingschemeEnName != null && !codingschemeEnName.equals("")) {
			query.setParameter("codingschemeEnName", codingschemeEnName);
		}
		if (conceptCode != null && !conceptCode.equals("")) {
			query.setParameter("conceptCode", conceptCode);
		}

		List<Object> list = query.getResultList();
		DictionaryDTO dto = getDictionaryDto(list);
		return dto;
	}

	private List<DictionaryDTO> getDictionaryDtoByObject(List<Object> list) {
		List<DictionaryDTO> etsList = new ArrayList<DictionaryDTO>();
		for (int i = 0; i < list.size(); i++) {
			Object[] o = (Object[]) list.get(i);
			DictionaryDTO dto = new DictionaryDTO();
			dto.setCodingschemeChName((String) o[0]);
			dto.setCodingschemeEnName((String) o[1]);
			dto.setCodingschemeOid((String) o[2]);
			dto.setVersionName((String) o[3]);
			dto.setConceptName((String) o[4]);
			dto.setConceptCode((String) o[5]);
			dto.setPinyinCode((String) o[6]);
			dto.setWubiCode((String) o[7]);
			
			Timestamp tsv=null;
			Timestamp tsc=null;
			if(null!=o[8])
			{
				tsv=(Timestamp)o[8] ;
			}
			if(null!=o[9])
			{
				tsc=(Timestamp)o[9] ;
			}
			
			if( o.length >= 11 ){
			    dto.setVersionId((String)o[10]);
			}
			
			if( o.length >= 12 ){
                dto.setFreqCounter((String)o[11]);
            }
			
			if( o.length >= 13 ){
                dto.setFreqInterval((String)o[12]);
            }
			
			if( o.length >= 14 ){
			    dto.setFreqIntervalUnit((String)o[13]);
            }
			
			if( o.length >= 15 ){
                dto.setAdministrations((String)o[14]);
            }
			
			if( o.length >= 16 ){
                dto.setDefaultSchedule((String)o[15]);
            }
			      
			if(null==tsv)
			{
				if(null==tsc)
				{
					etsList.add(dto);
				}else if(tsc.compareTo(new Timestamp(new Date().getTime()))>-1)
				{
					etsList.add(dto);
				}
			}else if(null!=tsv&&tsv.compareTo(new Timestamp(new Date().getTime()))>-1)
			{
				if(null==tsc)
				{
					etsList.add(dto);
				}else if(tsc.compareTo(new Timestamp(new Date().getTime()))>-1)
				{
					etsList.add(dto);
				}
			}else
			{
				break;
			}
		}
		return etsList;
	}

	private DictionaryDTO getDictionaryDto(List<Object> list) {
		DictionaryDTO dto = new DictionaryDTO();
		if (list != null && list.size() > 0) {
			Object[] o = (Object[]) list.get(0);
			
			dto.setCodingschemeChName((String) o[0]);
			dto.setCodingschemeEnName((String) o[1]);
			dto.setCodingschemeOid((String) o[2]);
			dto.setVersionName((String) o[3]);
			dto.setConceptName((String) o[4]);
			dto.setConceptCode((String) o[5]);
			dto.setPinyinCode((String) o[6]);
			dto.setWubiCode((String) o[7]);
		}
		return dto ;

	}

	public List<DictionaryDTO> getBulkConcepts(String as[],
			String codingschemeEnName, Boolean boolean1, Boolean boolean2) {
		String jpql = "select o from EtConcepts  o where 1=1 ";
		if (codingschemeEnName != null && !codingschemeEnName.equals("")) {
			jpql = " select o from EtConcepts o ,EtVersions ev,EtCodingSchemes ecs where ecs.codingschemeId = ev.etCodingSchemes.codingschemeId and ev.versionId = o.etVersions.versionId ";
			jpql += " and ecs.codingschemeEnName='" + codingschemeEnName + "'";

		}
		if (as != null && as.length > 0) {
			jpql += " and ( ";
			for (int i = 0; i < as.length; i++) {

				if (i == as.length - 1) {
					jpql += " o.conceptCode='" + as[i] + "' ";
				} else {
					jpql += " o.conceptCode='" + as[i] + "'  or ";
				}
			}
			jpql += " ) ";
		}
		Query query = entityManager2.createQuery(jpql);
		List<EtConcepts> list = query.getResultList();
		List<DictionaryDTO> etsList = getEtsDTOList(list);
		return etsList;
	}

	public List<DictionaryDTO> getBulkConcepts(String as[],
			String codingSchemeEnName, Boolean boolean1) {
		String jpql = "select o from EtConcepts  o where 1=1 ";
		if (codingSchemeEnName != null && !codingSchemeEnName.equals("")) {
			jpql = " select o from EtConcepts o ,EtVersions ev,EtCodingSchemes ecs where ecs.codingschemeId = ev.etCodingSchemes.codingschemeId and ev.versionId = o.etVersions.versionId ";

			jpql += " and ecs.codingschemeEnName='" + codingSchemeEnName + "'";

		}
		if (as != null && as.length > 0) {
			jpql += " and ( ";
			for (int i = 0; i < as.length; i++) {

				if (i == as.length - 1) {
					jpql += " o.conceptCode='" + as[i] + "' ";
				} else {
					jpql += " o.conceptCode='" + as[i] + "'  or ";
				}
			}
			jpql += " ) ";
		}
		Query query = entityManager2.createQuery(jpql);
		List<EtConcepts> list = query.getResultList();
		List<DictionaryDTO> etsList = getEtsDTOList(list);
		return etsList;
	}

	private List<DictionaryDTO> getEtsDTOList(List<EtConcepts> list) {
		List<DictionaryDTO> etsList = new ArrayList<DictionaryDTO>();
		if (list != null && list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				EtConcepts etConcepts = (EtConcepts) list.get(i);
				DictionaryDTO model = new DictionaryDTO();
				// model.setCodingschemeId(etConcepts.getEtVersions().getEtCodingSchemes().getCodingschemeId());
				model.setCodingschemeChName(etConcepts.getEtVersions()
						.getEtCodingSchemes().getCodingschemeChName());
				model.setCodingschemeEnName(etConcepts.getEtVersions()
						.getEtCodingSchemes().getCodingschemeEnName());
				model.setCodingschemeOid(etConcepts.getEtVersions()
						.getEtCodingSchemes().getCodingschemeOid());

				// model.setVersionId(etConcepts.getEtVersions().getVersionId());
				model.setVersionName(etConcepts.getEtVersions()
						.getVersionName());

				// model.setConceptId(etConcepts.getConceptId());
				model.setConceptCode(etConcepts.getConceptCode());
				model.setConceptName(etConcepts.getConceptName());
				etsList.add(model);
			}
		}
		return etsList;
	}

	public List<DictionaryDTO> getBulkConcepts(String as[],
			String codingSchemeEnName, String codingSchemeChName,
			Boolean boolean1, Boolean boolean2) {
		String jpql = "select o from EtConcepts  o where 1=1 ";
		if (codingSchemeEnName != null && !codingSchemeEnName.equals("")) {
			jpql = " select o from EtConcepts o ,EtVersions ev,EtCodingSchemes ecs where ecs.codingschemeId = ev.etCodingSchemes.codingschemeId and ev.versionId = o.etVersions.versionId ";
			jpql += " and ecs.codingschemeEnName='" + codingSchemeEnName + "'";

		}
		if (as != null && as.length > 0) {
			jpql += " and ( ";
			for (int i = 0; i < as.length; i++) {

				if (i == as.length - 1) {
					jpql += " o.conceptCode='" + as[i] + "' ";
				} else {
					jpql += " o.conceptCode='" + as[i] + "'  or ";
				}
			}
			jpql += " ) ";
		}

		Query query = entityManager2.createQuery(jpql);
		List<EtConcepts> list = query.getResultList();
		List<DictionaryDTO> etsList = getEtsDTOList(list);
		return etsList;
	}

	public List<DictionaryDTO> getPlace(String codingSchemeEnName, String code) {

		String jpql = " select  ecs.codingschemeChName,ecs.codingschemeEnName,ecs.codingschemeOid,ev.versionName,o.conceptName ,o.conceptCode,o.inputPy,o.inputWb "
				+ " from  EtConcepts o ,EtVersions ev,EtCodingSchemes ecs  "
				+ " where ecs.codingschemeId = ev.etCodingSchemes.codingschemeId and ev.versionId = o.etVersions.versionId  ";
		if (codingSchemeEnName != null && !codingSchemeEnName.equals("")) {
			jpql += " and  ecs.codingschemeEnName=:codingschemeEnName ";
		}

		String removeCode = code;
		if (code != null && !code.equals("")) {
			if (code.length() == 2) {
				jpql += " and o.conceptCode like '" + code + "%00' ";
				removeCode = removeCode.concat("0000");
			} else {
				jpql += " and o.conceptCode like '" + code + "%' ";
				removeCode = removeCode.concat("00");
			}
		} else {
			jpql += " and o.conceptCode like '%0000' ";
		}
		Query query = entityManager2.createQuery(jpql);
		if (codingSchemeEnName != null && !codingSchemeEnName.equals("")) {
			query.setParameter("codingschemeEnName", codingSchemeEnName);
		}
		List<Object> list2 = query.getResultList();
		List<DictionaryDTO> etsList = getDictionaryDtoByObject(list2,
				removeCode);
		return etsList;
	}

	private List<DictionaryDTO> getDictionaryDtoByObject(List<Object> list,
			String code) {
		List<DictionaryDTO> etsList = new ArrayList<DictionaryDTO>();
		for (int i = 0; i < list.size(); i++) {
			Object[] o = (Object[]) list.get(i);
			String conceptCode = (String) o[5];
			if (!code.equals(conceptCode)) {
				DictionaryDTO dto = new DictionaryDTO();
				dto.setCodingschemeChName((String) o[0]);
				dto.setCodingschemeEnName((String) o[1]);
				dto.setCodingschemeOid((String) o[2]);
				dto.setVersionName((String) o[3]);
				dto.setConceptName((String) o[4]);
				dto.setConceptCode((String) o[5]);
				dto.setPinyinCode((String) o[6]);
				dto.setWubiCode((String) o[7]);
				etsList.add(dto);
			}
		}
		return etsList;
	}

	// add by obama
	// 根据中文+英文得到字典dto
	public DictionaryDTO getConceptsDTO(String conceptCode, String conceptName) {
		StringBuilder jpql = new StringBuilder();
		jpql.append("select  ecs.codingschemeChName,ecs.codingschemeEnName,ecs.codingschemeOid,ev.versionName,o.conceptName ,o.conceptCode,o.inputPy,o.inputWb  ");
		jpql.append("from EtCodingSchemes ecs,EtVersions ev, EtConcepts o ");
		jpql.append("where  ecs.codingschemeId = ev.etCodingSchemes.codingschemeId and ev.versionId = o.etVersions.versionId  ");

		if (conceptName != null && !conceptName.equals("")) {
			jpql.append(" and o.conceptName=:conceptName ");
		}
		if (conceptCode != null && !conceptCode.equals("")) {
			jpql.append(" and o.conceptCode=:conceptCode ");
		}
		Query query = entityManager2.createQuery(jpql.toString());

		if (conceptName != null && !conceptName.equals("")) {
			query.setParameter("conceptName", conceptName);
		}
		if (conceptCode != null && !conceptCode.equals("")) {
			query.setParameter("conceptCode", conceptCode);
		}

		List<Object> list = query.getResultList();
		DictionaryDTO dto = getDictionaryDto(list);
		return dto;
	}
	// add by obama
	// 根据中文code和字典名称得到字典dto
	
	public DictionaryDTO getConceptsDTOByCodeAndEnname(
			String codingSchemesEnName, String conceptName) {
		StringBuilder jpql = new StringBuilder();
		jpql.append("select  ecs.codingschemeChName,ecs.codingschemeEnName,ecs.codingschemeOid,ev.versionName,o.conceptName ,o.conceptCode,o.inputPy,o.inputWb  ");
		jpql.append("from EtCodingSchemes ecs,EtVersions ev, EtConcepts o ");
		jpql.append("where  ecs.codingschemeId = ev.etCodingSchemes.codingschemeId and ev.versionId = o.etVersions.versionId  ");

		if (codingSchemesEnName != null && !codingSchemesEnName.equals("")) {
			jpql.append(" and ecs.codingschemeEnName=:codingschemeEnName ");
		}
		if (conceptName != null && !conceptName.equals("")) {
			jpql.append(" and o.conceptName=:conceptName ");
		}
		Query query = entityManager2.createQuery(jpql.toString());

		if (conceptName != null && !conceptName.equals("")) {
			query.setParameter("conceptName", conceptName);
		}
		if (codingSchemesEnName != null && !codingSchemesEnName.equals("")) {
			query.setParameter("codingschemeEnName", codingSchemesEnName);
		}

		List<Object> list = query.getResultList();
		DictionaryDTO dto = getDictionaryDto(list);
		return dto;
	}
	
	public List<DictionaryDTO> getBlobOrClob(String codingschemeChName,String codingschemeEnName,String versionName){
		
		String jpql = "select  ecs.codingschemeChName,ecs.codingschemeEnName,ecs.codingschemeOid,ev.versionName,o.conceptName ,o.conceptCode ,o.inputPy,o.inputWb,o.etBlob,o.etClob "
			+ "from EtCodingSchemes ecs,EtVersions ev, EtConcepts o "
			+ "where  ecs.codingschemeId = ev.etCodingSchemes.codingschemeId and ev.versionId = o.etVersions.versionId  ";

		if (codingschemeChName != null && !codingschemeChName.equals("")) {
			jpql += " and ecs.codingschemeChName=:codingschemeChName ";
		}
	
		if (codingschemeEnName != null && !codingschemeEnName.equals("")) {
			jpql += " and ecs.codingschemeEnName=:codingschemeEnName ";
		}
		if (versionName != null && !versionName.equals("")) {
			jpql += " and ev.versionName=:versionName ";
		}
		Query query = entityManager2.createQuery(jpql);
		if (codingschemeChName != null && !codingschemeChName.equals("")) {
			query.setParameter("codingschemeChName", codingschemeChName);
		}
		if (versionName != null && !versionName.equals("")) {
			query.setParameter("versionName", versionName);
		}
	
		List<Object> list = query.getResultList();
		
		List<DictionaryDTO> etsList = new ArrayList<DictionaryDTO>();
		if(list != null && list.size() > 0){
			for(int i = 0 ; i < list.size() ; i++ ){
				Object[] o = (Object[]) list.get(i);
				DictionaryDTO dto = new DictionaryDTO();
				dto.setCodingschemeChName((String) o[0]);
				dto.setCodingschemeEnName((String) o[1]);
				dto.setCodingschemeOid((String) o[2]);
				dto.setVersionName((String) o[3]);
				dto.setConceptName((String) o[4]);
				dto.setConceptCode((String) o[5]);
				dto.setPinyinCode((String) o[6]);
				dto.setWubiCode((String) o[7]);
				dto.setEtBlob((byte[])o[8]);
				dto.setEtClob((String)o[9]);
				etsList.add(dto);
			}
		}
		return etsList;
	}
	
	public DictionaryDTO getConceptsDTOBlobOrClob(String codingschemeEnName,
			String conceptCode){
		String jpql = "select  ecs.codingschemeChName,ecs.codingschemeEnName,ecs.codingschemeOid,ev.versionName,o.conceptName ,o.conceptCode,o.inputPy,o.inputWb ,o.etBlob,o.etClob "
			+ "from EtCodingSchemes ecs,EtVersions ev, EtConcepts o "
			+ "where  ecs.codingschemeId = ev.etCodingSchemes.codingschemeId and ev.versionId = o.etVersions.versionId  ";

		if (codingschemeEnName != null && !codingschemeEnName.equals("")) {
			jpql += " and ecs.codingschemeEnName=:codingschemeEnName ";
		}
		if (conceptCode != null && !conceptCode.equals("")) {
			jpql += " and o.conceptCode=:conceptCode ";
		}
		Query query = entityManager2.createQuery(jpql);
	
		if (codingschemeEnName != null && !codingschemeEnName.equals("")) {
			query.setParameter("codingschemeEnName", codingschemeEnName);
		}
		if (conceptCode != null && !conceptCode.equals("")) {
			query.setParameter("conceptCode", conceptCode);
		}

		List<Object> list = query.getResultList();
		if(list != null && list.size() > 0 ){
			
			Object[] o = (Object[]) list.get(0);
			DictionaryDTO dto = new DictionaryDTO();
			dto.setCodingschemeChName((String) o[0]);
			dto.setCodingschemeEnName((String) o[1]);
			dto.setCodingschemeOid((String) o[2]);
			dto.setVersionName((String) o[3]);
			dto.setConceptName((String) o[4]);
			dto.setConceptCode((String) o[5]);
			dto.setPinyinCode((String) o[6]);
			dto.setWubiCode((String) o[7]);
			dto.setEtBlob((byte[])o[8]);
			dto.setEtClob((String)o[9]);
			return dto;
		}
		return null;
	}

	private String getSimpleDictionaryAsXmlByString(List<Object> list)
    {
        StringBuilder xml = new StringBuilder("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
        if (ListUtil.isNotEmpty(list))
        {
            xml.append("<root>");
            for (Object result : list)
            {
                Object[] o = (Object[]) result;
                if (StrUtil.isEmpty((String) o[0]))
                {
                    o[0] = "";
                }
                if (StrUtil.isEmpty((String) o[1]))
                {
                    o[1] = "";
                }
                if (StrUtil.isEmpty((String) o[2]))
                {
                    o[2] = "";
                }
                xml.append("<item input_py=\"").append(o[0]).append("\" concept_name=\"").append(o[1]).append("\" concept_code=\"").append(o[2]).append("\">");
                if (StrUtil.isEmpty((String) o[3]))
                {
                    xml.append("<concept_id/>");
                }
                else
                {
                    xml.append("<concept_id>").append(o[3]).append("</concept_id>");
                }

               /* if (StrUtil.isEmpty((String) o[2]))
                {
                    xml.append("<version_id/>");
                }
                else
                {
                    xml.append("<version_id>").append(o[2]).append("</version_id>");
                }

                if (StrUtil.isEmpty((String) o[3]))
                {
                    xml.append("<concept_code/>");
                }
                else
                {
                    xml.append("<concept_code>").append(o[3]).append("</concept_code>");
                }*/
                xml.append("</item>");
            }
            xml.append("</root>");
        }
        return xml.toString();
    }
	
	@SuppressWarnings("unchecked")
    public List<DictionaryDTO> findDictionaryDtosByCSNameAndVersionNameAndConceptCodeAndModelCode(String codingschemeEnName,String versionName,String conceptCode,String modelCode) {
        String jpql = "select ecs.codingschemeChName,ecs.codingschemeEnName,ecs.codingschemeOid,ev.versionName,o.conceptName ,o.conceptCode,o.inputPy,o.inputWb ,ev.retiredDate,o.retiredDate,ev.versionId "
                + "from EtCodingSchemes ecs left join ecs.etVersionsList as ev left join ev.etConceptsList as o "
                + "where (ev.statusCode is null or ev.statusCode ='1') and (o.statusCode is null or o.statusCode = '1' )";
        
        if (StrUtil.isNotEmpty(codingschemeEnName))
        {
            jpql += " and ecs.codingschemeEnName=:codingschemeEnName ";
        }
        if (StrUtil.isNotEmpty(versionName))
        {
            jpql += " and ev.versionName=:versionName ";
        }else{
            jpql += " and ev.defaultFlag='1' ";
        }
        if (StrUtil.isNotEmpty(conceptCode)){
            jpql += " and o.conceptCode=:conceptCode ";
        }
        if (StrUtil.isNotEmpty(modelCode)){
            jpql += " and o.mqaModelCode=:modelCode ";
        }
        
        Query query = entityManager2.createQuery(jpql);
        if (StrUtil.isNotEmpty(codingschemeEnName)) {
            query.setParameter("codingschemeEnName", codingschemeEnName);
        }
        if (StrUtil.isNotEmpty(versionName)) {
            query.setParameter("versionName", versionName);
        }
        if (StrUtil.isNotEmpty(conceptCode)) {
            query.setParameter("conceptCode", conceptCode);
        }
        if (StrUtil.isNotEmpty(modelCode)) {
            query.setParameter("modelCode", modelCode);
        }
        List<Object> list = query.getResultList();
        List<DictionaryDTO> etsList = getDictionaryDtoByObject(list);
        return etsList;
	}
	
	@SuppressWarnings("unchecked")
    public List<DictionaryDTO> findDictionaryDtosListByCond(String codingschemeEnName,String versionName,String conceptCode,String conceptClass) {
        String jpql = "select ecs.codingschemeChName,ecs.codingschemeEnName,ecs.codingschemeOid,ev.versionName,o.conceptName ,o.conceptCode,o.inputPy,o.inputWb ,ev.retiredDate,o.retiredDate,ev.versionId "
                + "from EtCodingSchemes ecs left join ecs.etVersionsList as ev left join ev.etConceptsList as o "
                + "where (ev.statusCode is null or ev.statusCode ='1') and (o.statusCode is null or o.statusCode = '1' )";
        
        if (StrUtil.isNotEmpty(codingschemeEnName))
        {
            jpql += " and ecs.codingschemeEnName=:codingschemeEnName ";
        }
        if (StrUtil.isNotEmpty(versionName))
        {
            jpql += " and ev.versionName=:versionName ";
        }else{
            jpql += " and ev.defaultFlag='1' ";
        }
        if (StrUtil.isNotEmpty(conceptCode)){
            jpql += " and o.conceptCode=:conceptCode ";
        }
        if (StrUtil.isNotEmpty(conceptClass)){
            jpql += " and o.conceptClass=:conceptClass ";
        }
        
        Query query = entityManager2.createQuery(jpql);
        if (StrUtil.isNotEmpty(codingschemeEnName)) {
            query.setParameter("codingschemeEnName", codingschemeEnName);
        }
        if (StrUtil.isNotEmpty(versionName)) {
            query.setParameter("versionName", versionName);
        }
        if (StrUtil.isNotEmpty(conceptCode)) {
            query.setParameter("conceptCode", conceptCode);
        }
        if (StrUtil.isNotEmpty(conceptClass)) {
            query.setParameter("conceptClass", conceptClass);
        }
        List<Object> list = query.getResultList();
        List<DictionaryDTO> etsList = getDictionaryDtoByObject(list);
        return etsList;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<DictionaryDTO> findDictionaryDtosByConceptClass(String conceptClass)
    {   
        String jpql = "select ecs.codingschemeChName,ecs.codingschemeEnName,ecs.codingschemeOid,ev.versionName,o.conceptName ,o.conceptCode,o.inputPy,o.inputWb ,ev.retiredDate,o.retiredDate,ev.versionId,o.freqCounter,o.freqInterval,o.freqIntervalUnit,o.administrations,o.defaultSchedule "
            + "from EtCodingSchemes ecs left join ecs.etVersionsList as ev left join ev.etConceptsList as o "
            + "where (ev.statusCode is null or ev.statusCode ='1') and ev.defaultFlag='1' and (o.statusCode is null or o.statusCode = '1' )";
    
        if (StrUtil.isNotEmpty(conceptClass)){
            jpql += " and o.conceptClass= :conceptClass ";
        }
        
        Query query = entityManager2.createQuery(jpql);
        if (StrUtil.isNotEmpty(conceptClass)) {
            query.setParameter("conceptClass", conceptClass);
        }
        List<Object> list = query.getResultList();
        List<DictionaryDTO> etsList = getDictionaryDtoByObject(list);
        return etsList;
    }
    
    @SuppressWarnings("unchecked")
    public List<DictionaryDTO> findLeafDirAll(String codingschemeEnName,String versionName,String conceptCode,String conceptClass) {
        String jpql = "select ecs.codingschemeChName,ecs.codingschemeEnName,ecs.codingschemeOid,ev.versionName,o.conceptName ,o.conceptCode,o.inputPy,o.inputWb ,ev.retiredDate,o.retiredDate,ev.versionId,o.freqCounter,o.freqInterval,o.freqIntervalUnit,o.administrations,o.defaultSchedule "
                + "from EtCodingSchemes ecs left join ecs.etVersionsList as ev left join ev.etConceptsList as o "
                + "where (ev.statusCode is null or ev.statusCode ='1') and (o.statusCode is null or o.statusCode = '1' ) " 
                + "and o.conceptClass is not null conditionX1 conditionX2 conditionX3 conditionX4 and not exists( " 
                + "     select o2.conceptClass "
                + "     from EtCodingSchemes ecs2 left join ecs2.etVersionsList as ev2 left join ev2.etConceptsList as o2 "
                + "     where (ev2.statusCode is null or ev2.statusCode ='1') and (o2.statusCode is null or o2.statusCode = '1' ) " 
                + "     and o2.conceptClass is not null " 
                + "     conditionY1 conditionY2 conditionY3 conditionY4 " 
                + "     and o.conceptCode = o2.conceptClass "
                + ") " 
                + "order by o.conceptId ";
        
        if (StrUtil.isNotEmpty(codingschemeEnName))
        {
            jpql = jpql.replace("conditionX1", "and ecs.codingschemeEnName=:codingschemeEnName");
            jpql = jpql.replace("conditionY1", "and ecs2.codingschemeEnName=:codingschemeEnName");
        }else{
            jpql = jpql.replace("conditionX1", "");
            jpql = jpql.replace("conditionY1", "");
        }
        
        if (StrUtil.isNotEmpty(versionName))
        {
            jpql = jpql.replace("conditionX2", "and ev.versionName=:versionName");
            jpql = jpql.replace("conditionY2", "and ev2.versionName=:versionName");
        }else{
            jpql = jpql.replace("conditionX2", "and ev.defaultFlag='1'");
            jpql = jpql.replace("conditionY2", "and ev2.defaultFlag='1'");
        }
        if (StrUtil.isNotEmpty(conceptCode)){
            jpql = jpql.replace("conditionX3", "and o.conceptCode=:conceptCode");
            jpql = jpql.replace("conditionY3", "and o2.conceptCode=:conceptCode");
        }else{
            jpql = jpql.replace("conditionX3", "");
            jpql = jpql.replace("conditionY3", "");
        }
        if (StrUtil.isNotEmpty(conceptClass)){
            jpql = jpql.replace("conditionX4", "and o.conceptClass=:conceptClass");
            jpql = jpql.replace("conditionY4", "and o2.conceptClass=:conceptClass");
        }else{
            jpql = jpql.replace("conditionX4", "");
            jpql = jpql.replace("conditionY4", "");
        }
        
        Query query = entityManager2.createQuery(jpql);
        if (StrUtil.isNotEmpty(codingschemeEnName)) {
            query.setParameter("codingschemeEnName", codingschemeEnName);
        }
        if (StrUtil.isNotEmpty(versionName)) {
            query.setParameter("versionName", versionName);
        }
        if (StrUtil.isNotEmpty(conceptCode)) {
            query.setParameter("conceptCode", conceptCode);
        }
        if (StrUtil.isNotEmpty(conceptClass)) {
            query.setParameter("conceptClass", conceptClass);
        }
        List<Object> list = query.getResultList();
        List<DictionaryDTO> etsList = getDictionaryDtoByObject(list);
        return etsList;
    }
    
    @SuppressWarnings("unchecked")
    public List<String> findLeafDirCodeAll(String codingschemeEnName,String versionName,String conceptCode,String conceptClass) {
        String jpql = "select o.conceptCode "
                + "from EtCodingSchemes ecs left join ecs.etVersionsList as ev left join ev.etConceptsList as o "
                + "where (ev.statusCode is null or ev.statusCode ='1') and (o.statusCode is null or o.statusCode = '1' ) " 
                + "and o.conceptClass is not null conditionX1 conditionX2 conditionX3 conditionX4 and not exists( " 
                + "     select o2.conceptClass "
                + "     from EtCodingSchemes ecs2 left join ecs2.etVersionsList as ev2 left join ev2.etConceptsList as o2 "
                + "     where (ev2.statusCode is null or ev2.statusCode ='1') and (o2.statusCode is null or o2.statusCode = '1' ) " 
                + "     and o2.conceptClass is not null " 
                + "     conditionY1 conditionY2 conditionY3 conditionY4 " 
                + "     and o.conceptCode = o2.conceptClass "
                + ") " 
                + "";
        
        if (StrUtil.isNotEmpty(codingschemeEnName))
        {
            jpql = jpql.replace("conditionX1", "and ecs.codingschemeEnName=:codingschemeEnName");
            jpql = jpql.replace("conditionY1", "and ecs2.codingschemeEnName=:codingschemeEnName");
        }else{
            jpql = jpql.replace("conditionX1", "");
            jpql = jpql.replace("conditionY1", "");
        }
        
        if (StrUtil.isNotEmpty(versionName))
        {
            jpql = jpql.replace("conditionX2", "and ev.versionName=:versionName");
            jpql = jpql.replace("conditionY2", "and ev2.versionName=:versionName");
        }else{
            jpql = jpql.replace("conditionX2", "and ev.defaultFlag='1'");
            jpql = jpql.replace("conditionY2", "and ev2.defaultFlag='1'");
        }
        if (StrUtil.isNotEmpty(conceptCode)){
            jpql = jpql.replace("conditionX3", "and o.conceptCode=:conceptCode");
            jpql = jpql.replace("conditionY3", "and o2.conceptCode=:conceptCode");
        }else{
            jpql = jpql.replace("conditionX3", "");
            jpql = jpql.replace("conditionY3", "");
        }
        if (StrUtil.isNotEmpty(conceptClass)){
            jpql = jpql.replace("conditionX4", "and o.conceptClass=:conceptClass");
            jpql = jpql.replace("conditionY4", "and o2.conceptClass=:conceptClass");
        }else{
            jpql = jpql.replace("conditionX4", "");
            jpql = jpql.replace("conditionY4", "");
        }
        
        Query query = entityManager2.createQuery(jpql);
        if (StrUtil.isNotEmpty(codingschemeEnName)) {
            query.setParameter("codingschemeEnName", codingschemeEnName);
        }
        if (StrUtil.isNotEmpty(versionName)) {
            query.setParameter("versionName", versionName);
        }
        if (StrUtil.isNotEmpty(conceptCode)) {
            query.setParameter("conceptCode", conceptCode);
        }
        if (StrUtil.isNotEmpty(conceptClass)) {
            query.setParameter("conceptClass", conceptClass);
        }
        List<String> list = query.getResultList();
        return list;
    }
}
